package cn.schina.dbfw.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import cn.schina.dbfw.dao.DaoConn;
import cn.schina.dbfw.dao.JDBCConnection;

public class SensiObjAnalysisDao {
	ArrayList<SensiObjAnalysisResult> sensiResultArray = new ArrayList<SensiObjAnalysisResult>();
	JDBCConnection conn = null;
	String sql;
	int baselineid = 0;
	int first_flag = 0;
	public ArrayList<SensiObjAnalysisResult> getSensiObjAnalysis(DBWithDbfw p_dbwithdbfw,
			ArrayList<QueryParam> p_queryParams,
			DBObjectsParam p_objectParam,
			DateParam p_date) 
		throws SQLException
	{
		conn = DaoConn.createDBConnection();
		sql = "select distinct t4.*,IFNULL(sqltype.typename,'未知') sqltype " +
				"from ( " +
				"select distinct t3.*,IF(sql_alarms_forall.sqlid,1,0) alarm_flag_forall," +
				"IF(sql_alarms_rowlimits.sqlid,1,0) alarm_flag_rowlimits " +
				"from ( " +
				"select sql_t.*,IFNULL(risk.blackorwhite,0) risk_borw,IFNULL(risk.threat_level,0) risk_tlevel," +
				"IFNULL(ac.threat_level,0) ac_tlevel,IFNULL(vpatch.threat_level,0) vpatch_tlevel " +
				"from ( " +
				"select t2.*,sqllog.sql_text,sqllog.sqltype_1,sqllog.sqltype_2 " +
				"from sqllog_event sqllog," +
				"(select *,count(sqlid) session_count from (" +
				"select (case obj.owner when '' then '[ANY]' else obj.owner end) owner," +
				"obj.object_name,sql_sess.dbfw_inst_id,sql_sess.sqlid,sess.db_server,upper(sess.db_user) dbuser," +
				"sess.db_client,sess.sid,sess.program_nm " +
				"from objects obj,sql_sessions sql_sess,sessionlog_event sess " +
				"where obj.object_type=2 and ";
		//obj.owner='' and
		if(p_objectParam.getOwnername()=="[ANY]")
		{
			sql = sql + "obj.owner='' and ";			
		}
		else
		{
			sql = sql + "obj.owner='"+p_objectParam.getOwnername()+"' and ";	
		}
		//obj.object_name='PERFORMANCE_C' and
		sql = sql + "obj.object_name='"+p_objectParam.getObjectname()+"' and ";
		sql = sql + "sql_sess.sqlid=obj.database_id and ";
		sql = sql + "sql_sess.session_id=sess.session_id and ";
		//UPPER(sess.db_instance_name) =UPPER('ORCL24') and 
		sql = sql + "UPPER(sess.db_instance_name)=UPPER('"+p_dbwithdbfw.getName()+"') and ";
		//sess.source_id in (0,1) and
		sql = sql + "sess.source_id in (0";
		Iterator<Dbfw> it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
			//顺便取得baselineid
			if(tmp_dbfw.getBaselineid()>0)
			{
				baselineid = tmp_dbfw.getBaselineid();
			}
		}
		sql = sql + ") and ";
		//sess.db_client in ('','192.168.1.245','192.168.1.247') and
		first_flag = 0;
		Iterator<QueryParam> it_qp_ip = p_queryParams.iterator();
		if(it_qp_ip.hasNext())
		{			
			while(it_qp_ip.hasNext())
			{
				QueryParam tmp_param = it_qp_ip.next();
				if(tmp_param.getParam_code()==2)
				{
					////有clientip参数
					if(first_flag==0)
					{
						sql = sql + "sess.db_client in (''";
						first_flag = 1;
					}
					sql = sql + ",'"+tmp_param.getParam_value()+"'";
				}
			}
			if(first_flag==1)
			{
				sql = sql + ") and ";
			}
		}
		
		//sess.clientmac in ('',UPPER('705AB65DE811'),UPPER('705AB65DE812')) and
		first_flag = 0;
		Iterator<QueryParam> it_qp_mac = p_queryParams.iterator();
		//sql = sql + "sess.clientmac in (''";
		if(it_qp_mac.hasNext())
		{
			while(it_qp_mac.hasNext())
			{
				QueryParam tmp_param = it_qp_mac.next();
				if(tmp_param.getParam_code()==3)
				{
					//有MAC参数
					if(first_flag==0)
					{
						sql = sql + "sess.clientmac in (''";
						first_flag = 1;
					}
					sql = sql + ",UPPER('"+tmp_param.getParam_value()+"')";
				}
			}
			if(first_flag==1)
			{
				sql = sql + ") and ";
			}
		}
		
		//UPPER(sess.program_nm) in ('','SQLPLUS.EXE','PLSQLDEV.EXE') and
		first_flag = 0;
		Iterator<QueryParam> it_qp_app = p_queryParams.iterator();
		if(it_qp_app.hasNext())
		{
			while(it_qp_app.hasNext())
			{
				QueryParam tmp_param = it_qp_app.next();
				if(tmp_param.getParam_code()==4 || tmp_param.getParam_code()==5)
				{
					//有卖主工具程序名,应用程序名
					if(first_flag==0)
					{
						sql = sql = sql + "UPPER(sess.program_nm) in (''";
						first_flag = 1;
					}
					sql = sql + ",UPPER('"+tmp_param.getParam_value()+"')";
				}
			}
			if(first_flag==1)
			{
				sql = sql + ") and ";
			}
		}
		
		//UPPER(sess.db_user) in ('','CSC') and
		first_flag = 0;
		Iterator<QueryParam> it_qp_user = p_queryParams.iterator();
		if(it_qp_user.hasNext())
		{
			while(it_qp_user.hasNext())
			{
				QueryParam tmp_param = it_qp_user.next();
				if(tmp_param.getParam_code()==1)
				{
					//数据库用户名
					if(first_flag==0)
					{
						sql = sql + "UPPER(sess.db_user) in (''";
						first_flag = 1;
					}
					sql = sql + ",UPPER('"+tmp_param.getParam_value()+"')";
				}
			}
			if(first_flag==1)
			{
				sql = sql + ") and ";
			}
		}
		
		//sess.connect_time between '2012-12-20' and '2012-12-26'
		sql = sql + "sess.connect_time between '"+p_date.getStartDate()+"' and '"+p_date.getEndDate()+"' ";
		sql = sql + ")t group by sqlid,dbuser,db_client,sid,program_nm ) t2 " +
				"where sqllog.sqlid=t2.sqlid and " +
				"sqllog.dbfw_inst_id=t2.dbfw_inst_id ) " +
				"sql_t left join (sqllog_threat_risk risk,sqllog_threat_ac ac,sqllog_threat_vpatch vpatch) on ";
		//sql_t.sqlid=risk.sqlid and risk.baseline_id=174 and
		sql = sql + "sql_t.sqlid=risk.sqlid and risk.baseline_id="+baselineid+" and ";
		//sql_t.sqlid=ac.sqlid and ac.baseline_id=174 and 
		sql = sql + "sql_t.sqlid=ac.sqlid and ac.baseline_id="+baselineid+" and ";
		//sql_t.sqlid=vpatch.sqlid and vpatch.baseline_id=174
		sql = sql + "sql_t.sqlid=vpatch.sqlid and vpatch.baseline_id="+baselineid+" ";
		sql = sql + ") t3 left join (sql_alarms_forall) on ";
		//t3.sqlid=sql_alarms_forall.sqlid and sql_alarms_forall.baseline_id=174
		sql = sql + "t3.sqlid=sql_alarms_forall.sqlid and sql_alarms_forall.baseline_id="+baselineid+" ";
		sql = sql + "left join (sql_alarms_rowlimits) on ";
		//t3.sqlid=sql_alarms_rowlimits.sqlid and sql_alarms_rowlimits.baseline_id=174 
		sql = sql + "t3.sqlid=sql_alarms_rowlimits.sqlid and sql_alarms_rowlimits.baseline_id="+baselineid+" ";
		sql = sql + ") t4 left join(sqltype,sqltype_detail detail) ";
		sql = sql + "on t4.sqltype_1=detail.sqltype_1 and t4.sqltype_2=detail.sqltype_2 and " +
				"sqltype.typeflag=detail.typeid "+
				"order by dbuser,db_client,program_nm,session_count desc";
		//System.out.println(sql);
		PreparedStatement statement = conn.getConnection()
				.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next())
		{
			SensiObjAnalysisResult _sensiresult = new SensiObjAnalysisResult();
			_sensiresult.setOwner(resultSet.getString("owner"));
			_sensiresult.setObject_name(resultSet.getString("object_name"));
			_sensiresult.setDbfw_inst_id(resultSet.getInt("dbfw_inst_id"));
			_sensiresult.setSqlid(resultSet.getInt("sqlid"));
			_sensiresult.setDb_server(resultSet.getString("db_server"));
			_sensiresult.setDbuser(resultSet.getString("dbuser"));
			_sensiresult.setDb_client(resultSet.getString("db_client"));
			_sensiresult.setSid(resultSet.getString("sid"));
			_sensiresult.setProgram_nm(resultSet.getString("program_nm"));
			_sensiresult.setSession_count(resultSet.getInt("session_count"));
			_sensiresult.setSql_text(resultSet.getString("sql_text"));
			_sensiresult.setSqltype_1(resultSet.getInt("sqltype_1"));
			_sensiresult.setSqltype_2(resultSet.getInt("sqltype_2"));
			_sensiresult.setRisk_borw(resultSet.getInt("risk_borw"));
			_sensiresult.setRisk_tlevel(resultSet.getInt("risk_tlevel"));
			_sensiresult.setAc_tlevel(resultSet.getInt("ac_tlevel"));
			_sensiresult.setVpatch_tlevel(resultSet.getInt("vpatch_tlevel"));
			_sensiresult.setAlarm_flag_forall(resultSet.getInt("alarm_flag_forall"));
			_sensiresult.setAlarm_flag_rowlimits(resultSet.getInt("alarm_flag_rowlimits"));
			_sensiresult.setSqltype(resultSet.getString("sqltype"));
			System.out.println(_sensiresult.getHelp_borw());
			System.out.println(_sensiresult.getHelp_tlevel());
			sensiResultArray.add(_sensiresult);
		}
		statement.close();
		return sensiResultArray;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ArrayList<SensiObjAnalysisResult> sensiResultArray = new ArrayList<SensiObjAnalysisResult>();
		SensiObjAnalysisDao _sensianalysisdao;
		_sensianalysisdao = new SensiObjAnalysisDao();
		DBWithDbfw p_dbwithdbfw = new DBWithDbfw();
		Dbfw _dbfw;
		ArrayList<QueryParam> p_queryParams = new ArrayList<QueryParam>();
		DBObjectsParam p_objectParam = new DBObjectsParam();
		DateParam p_date = new DateParam();
		p_dbwithdbfw.setId(1);
		p_dbwithdbfw.setName("orcl24");
		p_dbwithdbfw.setDbtype("1");
		p_dbwithdbfw.setDbversion("10020001");
		p_dbwithdbfw.setDialect(1);
		_dbfw = new Dbfw();
		_dbfw.setInstid(1);
		_dbfw.setName("dbfw24");
		_dbfw.setBaselineid(174);
		_dbfw.setAddress("192.168.1.24");
		p_dbwithdbfw.addDbfwForDB(_dbfw);
		QueryParam _queryParam;		
		//1-数据库用户
		_queryParam = new QueryParam();
		_queryParam.setParam_code(1);
		_queryParam.setParam_value("CSC");
		p_queryParams.add(_queryParam);
		_queryParam = new QueryParam();
		_queryParam.setParam_code(1);
		_queryParam.setParam_value("BENCHMARK");
		p_queryParams.add(_queryParam);
		//2-客户端IP地址
		_queryParam = new QueryParam();
		_queryParam.setParam_code(2);
		_queryParam.setParam_value("192.168.1.245");
		p_queryParams.add(_queryParam);
		_queryParam = new QueryParam();
		_queryParam.setParam_code(2);
		_queryParam.setParam_value("192.168.1.247");
		p_queryParams.add(_queryParam);
		//3-客户端MAC地址(clientmac)
//		_queryParam = new QueryParam();
//		_queryParam.setParam_code(3);
//		_queryParam.setParam_value("705AB65DE811");
//		p_queryParams.add(_queryParam);
//		_queryParam = new QueryParam();
//		_queryParam.setParam_code(3);
//		_queryParam.setParam_value("705AB65DE812");
//		p_queryParams.add(_queryParam);
		//4-卖主工具程序名(program_nm)
		_queryParam = new QueryParam();
		_queryParam.setParam_code(4);
		_queryParam.setParam_value("SQLPLUS.EXE");
		p_queryParams.add(_queryParam);
		_queryParam = new QueryParam();
		_queryParam.setParam_code(4);
		_queryParam.setParam_value("PLSQLDEV.EXE");
		p_queryParams.add(_queryParam);
		p_objectParam.setOwnername("[ANY]");
		p_objectParam.setObjectname("PERFORMANCE_C");
		p_date.setStartDate("2012-12-20");
		p_date.setEndDate("2012-12-26");
		try {
			sensiResultArray = _sensianalysisdao.getSensiObjAnalysis(p_dbwithdbfw, p_queryParams, p_objectParam, p_date);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
